/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ejb.sessionBeans;

import estadisticas.AvisosMesEst;
import estadisticas.TiempoAtencionEst;
import estadisticas.TiposAvisoEst;
import estadisticas.TiposSolucionEst;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import modelo.Aviso;

/**
 *
 * @author inftel
 */
@Stateless
public class AvisoFacade extends AbstractFacade<Aviso> implements AvisoFacadeRemote {

    @PersistenceContext(unitName = "TelePaSOS-ejbPU")
    private EntityManager em;

    protected EntityManager getEntityManager() {
        return em;
    }

    public AvisoFacade() {
        super(Aviso.class);
    }

    @Override
    public List<Aviso> findByEstado(String estado) {
        String query = "SELECT a FROM Aviso a WHERE a.idEstado.descripcion = :estado";
        return em.createQuery(query).setParameter("estado", estado).getResultList();
    }
    
    @Override
    public List<Aviso> findByUsuario(int id_usuario){
        List<Aviso> list;

        list = em.createQuery("SELECT a FROM Aviso a WHERE a.idUsuario.idUsuario = :id").setParameter("id", id_usuario).getResultList();
        return list;
    }

    @Override
    public List<AvisosMesEst> getAvisosMes() {
        List<AvisosMesEst> list = new ArrayList<AvisosMesEst>();

        Iterator results = em.createQuery("SELECT COUNT(a), FUNC('MONTH',a.horaAviso) FROM Aviso a GROUP BY FUNC('MONTH',a.horaAviso)").getResultList().iterator();
        while (results.hasNext()) {
            Object[] row = (Object[]) results.next();
            Long numAviso = (Long) row[0];
            Integer mes = (Integer) row[1];
            list.add(new AvisosMesEst(numAviso, mes));
        }
        return list;
    }

    @Override
    public List<TiempoAtencionEst> getTiempoAtencion() {
        List<TiempoAtencionEst> list = new ArrayList<TiempoAtencionEst>();
        /*
         * Iterator results = em.createQuery("SELECT
         * FUNC('TIMESTAMPDIFF',MINUTE," + "a.horaAviso,a.horaAtencion) FROM
         * Aviso a " + "WHERE a.horaAtencion IS NOT
         * NULL").getResultList().iterator();
         */
        Iterator results = em.createQuery("SELECT a.horaAviso, a.horaAtencion"
                + " FROM Aviso a WHERE a.horaAtencion IS NOT NULL")
               .getResultList().iterator();

        while (results.hasNext()) {
            Object[] row = (Object[]) results.next();
            java.util.Date horaAviso = (java.util.Date) row[0];
            java.util.Date horaAtencion = (java.util.Date) row[1];
            list.add(new TiempoAtencionEst(horaAviso, horaAtencion));
        }
        return list;
    }

    @Override
    public List<TiposAvisoEst> getTiposAviso() {
        List<TiposAvisoEst> list = new ArrayList<TiposAvisoEst>();

        Iterator results = em.createQuery("SELECT COUNT(a), a.idTipoaviso.descripcion "
                + "FROM Aviso a GROUP BY a.idTipoaviso").getResultList().iterator();

        while (results.hasNext()) {
            Object[] row = (Object[]) results.next();
            Long numAviso = (Long) row[0];
            String tipoAviso = (String) row[1];
            list.add(new TiposAvisoEst(numAviso, tipoAviso));
        }
        return list;
    }

    @Override
    public List<TiposSolucionEst> getTiposSolucion() {
        List<TiposSolucionEst> list = new ArrayList<TiposSolucionEst>();

        Iterator results = em.createQuery("SELECT COUNT(a), a.idSolucion.descripcion FROM Aviso a GROUP BY a.idSolucion").getResultList().iterator();

        while (results.hasNext()) {
            Object[] row = (Object[]) results.next();
            Long numAviso = (Long) row[0];
            String tipoSolucion = (String) row[1];
            list.add(new TiposSolucionEst(numAviso, tipoSolucion));
        }
        return list;
    }
    
    @Override
    public int countByEstado(String estado) {
        String query = "SELECT COUNT(a) FROM Aviso a WHERE a.idEstado.descripcion = :estado";
        return ((Long)em.createQuery(query).setParameter("estado", estado).getSingleResult()).intValue();
    }
}
